package cj.web.admin.dao;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;

@DAO
public interface ReportsDAO {

	@SQL("select a.name,a.num,IFNULL(m.paidCount,0) as paidCount,IFNULL(n.notPayCount,0) as notPayCount,(IFNULL(m.paidCount,0) + IFNULL(n.notPayCount,0)) as used from School a "
			+ "LEFT JOIN "
			+ "(select b.schoolName,COUNT(b.schoolName) as paidCount from GameOrder b WHERE b.deleteFlag=1 AND b.gameId=:1 AND b.status=1 AND b.createAt >= :2 AND b.createAt < :3 GROUP BY b.schoolName) m ON a.name=m.schoolName "
			+ "LEFT JOIN "
			+ "(select b.schoolName,COUNT(b.schoolName) as notPayCount from GameOrder b WHERE b.deleteFlag=1 AND b.gameId=:1 AND b.status=0 AND b.createAt >= :2 AND b.createAt < :3 GROUP BY b.schoolName) n ON a.name=n.schoolName")
    List<Map> queryReportsBMTJZ(int gameId,String startAt,String endAt);
	
	@SQL("select a.name,a.num,IFNULL(m.paidCount,0) as paidCount,IFNULL(n.notPayCount,0) as notPayCount,(IFNULL(m.paidCount,0) + IFNULL(n.notPayCount,0)) as used "
			+ "from (select SUM(l.num) as num,s.name from School l JOIN Area s ON l.areaId=s.id GROUP BY l.areaId) a "
			+ "LEFT JOIN "
			+ "(select b.areaName,COUNT(b.areaName) as paidCount from GameOrder b WHERE b.deleteFlag=1 AND b.gameId=:1 and b.status=1 AND b.createAt >= :2 AND b.createAt < :3 GROUP BY b.areaName) m ON a.name= m.areaName "
			+ "LEFT JOIN "
			+ "(select b.areaName,COUNT(b.areaName) as notPayCount from GameOrder b WHERE b.deleteFlag=1 AND b.gameId=:1 and b.status=0 AND b.createAt >= :2 AND b.createAt < :3 GROUP BY b.areaName) n ON a.name= n.areaName ")
    List<Map> queryReportsBMTJR(int gameId,String startAt,String endAt);
	
	@SQL("select a.name,b.sellcount from SCGoodsItems a LEFT JOIN "
			+ "(select a.goodsItemId,sum(a.count) as sellcount from SCOrderItems a join SCOrder b on a.orderId=b.id "
			+ "where b.deleteFlag=1 and b.status>1 and b.payAt>=:1 and b.payAt<:2 group by a.goodsItemId) b on a.id = b.goodsItemId ")
    List<Map> queryReportsXLTJ(String startAt,String endAt);
	
	//
	@SQL("SELECT areaName,count(1) AS count FROM GameOrder WHERE gameId=:1 AND createAt >= :2 AND createAt < :3 GROUP BY areaName ")
    List<Map> queryReportsBMAreaTJ(int gameId,String startAt,String endAt);
	
	@SQL("SELECT areaName,area,count(1) AS count FROM GameOrder WHERE gameId=:1 AND createAt >= :2 AND createAt < :3 GROUP BY areaName,area ")
    List<Map> queryReportsBMCrossTJ(int gameId,String startAt,String endAt);
	
	@SQL("SELECT a.area,a.school,f.count0 AS totalNum,b.count1 AS firstPrize,c.count2 AS secondPrize,d.count3 AS thridPrize,e.count4 AS specialAward "
			+ "FROM (SELECT area,school FROM ReportCard WHERE gameId =:1 GROUP BY area,school ORDER BY area DESC) a "
			+ "LEFT JOIN (SELECT area,school,count(1) AS count0 FROM ReportCard WHERE gameId =:1 GROUP BY area,school) f ON a.school=f.school AND a.area=f.area "
			+ "LEFT JOIN (SELECT area,school,count(1) AS count1 FROM ReportCard WHERE gameId =:1 AND `level`='一等奖' GROUP BY area,school) b ON a.school=b.school AND a.area=b.area "
			+ "LEFT JOIN (SELECT area,school,count(1) AS count2 FROM ReportCard WHERE gameId =:1 AND `level`='二等奖' GROUP BY area,school) c ON a.school=c.school AND a.area=c.area "
			+ "LEFT JOIN (SELECT area,school,count(1) AS count3 FROM ReportCard WHERE gameId =:1 AND `level`='三等奖' GROUP BY area,school) d ON a.school=d.school AND a.area=d.area "
			+ "LEFT JOIN (SELECT area,school,count(1) AS count4 FROM ReportCard WHERE gameId =:1 AND `level`='特等奖' GROUP BY area,school) e ON a.school=e.school AND a.area=e.area "
			+ "ORDER BY a.area DESC,f.count0 DESC ")
    List<Map> queryReportsBMSchoolScoreTJ(int gameId);
	
	@SQL("SELECT a.area,a.school,a.teacher,f.count0 AS totalNum,b.count1 AS firstPrize,c.count2 AS secondPrize,d.count3 AS thridPrize,e.count4 AS specialAward "
			+ "FROM (SELECT area,school,teacher FROM ReportCard WHERE gameId =:1 GROUP BY area,school,teacher ORDER BY area DESC LIMIT :2,:3 ) a "
			+ "LEFT JOIN (SELECT area,school,teacher,count(1) AS count0 FROM ReportCard WHERE gameId =:1 GROUP BY area,school,teacher) f ON a.school=f.school AND a.area=f.area AND a.teacher=f.teacher "
			+ "LEFT JOIN (SELECT area,school,teacher,count(1) AS count1 FROM ReportCard WHERE gameId =:1 AND `level`='一等奖' group by area,school,teacher) b ON a.school=b.school AND a.area=b.area AND a.teacher=b.teacher "
			+ "LEFT JOIN (SELECT area,school,teacher,count(1) AS count2 FROM ReportCard WHERE gameId =:1 AND `level`='二等奖' group by area,school,teacher) c ON a.school=c.school AND a.area=c.area AND a.teacher=c.teacher "
			+ "LEFT JOIN (SELECT area,school,teacher,count(1) AS count3 FROM ReportCard WHERE gameId =:1 AND `level`='三等奖' group by area,school,teacher) d ON a.school=d.school AND a.area=d.area AND a.teacher=d.teacher "
			+ "LEFT JOIN (SELECT area,school,teacher,count(1) AS count4 FROM ReportCard WHERE gameId =:1 AND `level`='特等奖' group by area,school,teacher) e ON a.school=e.school AND a.area=e.area AND a.teacher=e.teacher "
			+ "ORDER BY a.area DESC,a.school DESC ")
    List<Map> queryReportsBMSchoolTeacherTJ(int gameId,int start,int length);
	
	@SQL("SELECT COUNT(1) FROM (SELECT area,school,teacher FROM ReportCard WHERE gameId =:1 GROUP BY area,school,teacher )a ")
    int queryReportsBMSchoolTeacherTJSize(int gameId);
	
	@SQL("select a.area,f.count0 AS totalNum,b.count1 AS firstPrize,c.count2 AS secondPrize,d.count3 AS thridPrize,e.count4 AS specialAward "
			+ "from (select area from ReportCard where gameId =:1 GROUP BY area order by area desc ) a "
			+ "left join (select area,count(1) as count0 from ReportCard where gameId =:1 group by area order by area desc) f on a.area=f.area "
			+ "left join (select area,count(1) as count1 from ReportCard where gameId =:1 and `level`='一等奖' group by area order by area desc) b on a.area=b.area "
			+ "left join (select area,count(1) as count2 from ReportCard where gameId =:1 and `level`='二等奖' group by area order by area desc) c on a.area=c.area "
			+ "left join (select area,count(1) as count3 from ReportCard where gameId =:1 and `level`='三等奖' group by area order by area desc) d on a.area=d.area "
			+ "left join (select area,count(1) as count4 from ReportCard where gameId =:1 and `level`='特等奖' group by area order by area desc) e on a.area=e.area "
			+ "ORDER BY a.area desc ")
    List<Map> queryReportsBMAreaScoreTJ(int gameId);
}